package database;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

import sharedspace.*;
/**
 * Responsible for data transition from SQL server to the server
 * @author G3
 *
 */
public class StudentControl {
	
	private Student student = null;
	private StudentInTest studentInTest = null;
	private Choices choices = null;
	private List<Student> studentList = null;
	private List<StudentInTest> studentInTestList = null; 
	private List<Choices> choicesList = null;
	private List<ApprovingGrade> aList = null;
	private ApprovingGrade approvingGrade = null;
	
	
	public StudentControl(){	
	}
	
	/**
	 * Generate list of all students
	 * @return ArrayList<Student> list
	 */
	public List<Student> getStudentList()
	{
		int studentSize = JDBC.countStatment("SELECT count(*) FROM student s;");
		studentList = new ArrayList<Student>();
		Object[][] result = new Object[studentSize][1];
		JDBC.selectStatmentMatrix("SELECT s.userID FROM student s;", result, 1, studentSize);
		for(int i = 0; i<studentSize; i++)
		{
			student = new Student();
			getStudentByStudentID(result[i][0].toString());
			studentList.add(student);
		}
		
		return studentList;
	}
	
	/**
	 * Generate list of students belonging to specific courseID, Year and Semester
	 * @param courseID
	 * @param year
	 * @param semester
	 * @return ArrayList<Student> list
	 */
	public List<Student> getStudentListByCourseID(String courseID,int year,int semester)
	{
		int studentSize = JDBC.countStatment("SELECT count(*) FROM studentincourse sic " +
											 "WHERE sic.studentCourseYear="+year+" AND sic.studentCourseSemester="+semester+" AND sic.courseID="+courseID+";");
		studentList = new ArrayList<Student>();
		Object[][] result = new Object[studentSize][1];
		JDBC.selectStatmentMatrix("SELECT sic.studentID FROM studentincourse sic " +
				 				  "WHERE sic.studentCourseYear="+year+" AND sic.studentCourseSemester="+semester+" AND sic.courseID="+courseID+";",
				 				  result, 1, studentSize);
		for(int i = 0; i<studentSize; i++)
		{
			student = new Student();
			getStudentByStudentID(result[i][0].toString());
			studentList.add(student);
		}
		
		return studentList;
	}
	
	/**
	 * Generate list of students belonging to specific executeTestID
	 * @param executeTestID
	 * @return ArrayList<StudentInTest> list
	 */
	public List<StudentInTest> getStudentListByExecuteTestID(String executeTestID)
	{
		int studentSize = JDBC.countStatment("SELECT count(*) FROM studentintest sit " +
											 "WHERE sit.executeTestID='" + executeTestID + "';");
		studentInTestList = new ArrayList<StudentInTest>();
		Object[][] result = new Object[studentSize][StudentInTest.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM studentintest sit " +
								  "WHERE sit.executeTestID='" + executeTestID + "';", result, StudentInTest.NUM_OF_COLUMNS, studentSize);
		for(int i = 0; i<studentSize; i++)
		{
			studentInTest = new StudentInTest();
			setStudentInTest(result, i);
			studentInTestList.add(studentInTest);
		}
		
		return studentInTestList;
		
	}
	
	/**
	 * Generate list of choices that specific student made in specific exam
	 * @param executeTestID
	 * @param studentID
	 * @return ArrayList<Choices> list
	 */
	public List<Choices> getStudentChoicesListByExexuteTestID(String executeTestID,String studentID)
	{
		int choiceSize = JDBC.countStatment("SELECT count(*) FROM choices c " +
											"WHERE c.executeTestID="+executeTestID+" AND c.studentID="+studentID+";");
		choicesList = new ArrayList<Choices>();
		Object[][] result = new Object[choiceSize][Choices.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM choices c " +
								  "WHERE c.executeTestID="+executeTestID+" AND c.studentID="+studentID+";",
								  result, Choices.NUM_OF_COLUMNS, choiceSize);
		for(int i = 0; i<choiceSize; i++)
		{
			choices = new Choices();
			setCoices(result, i);
			choicesList.add(choices);
		}
		return choicesList;
	}
	
	
	/**
	 * Generate list of ApprovingGrade of specific student 
	 * @param studentID
	 * @return ArrayList<ApprovingGrade> list
	 */
	public List<ApprovingGrade> getApprovedGradeByStudentID(String studentID)
	{
		
		aList = new ArrayList<ApprovingGrade>();
		int approveSize = JDBC.countStatment("SELECT count(*) FROM aprovinggrade ag " +
											 "WHERE ag.studentID='"+studentID+"' AND ag.teacherApproveComplete=true;");
		Object[][] result = new Object[approveSize][ApprovingGrade.NUM_OF_COLOUMS];
		JDBC.selectStatmentMatrix("SELECT * FROM aprovinggrade ag " +
								  "WHERE ag.studentID='"+studentID+"' AND ag.teacherApproveComplete=true;"
											 , result, ApprovingGrade.NUM_OF_COLOUMS, approveSize);
		for (int i = 0; i<approveSize; i++)
		{
			setApprovingGrade(result, i);
			aList.add(approvingGrade);	
		}
		return aList;
	}
	
	/**
	 * Generate list of ApprovingGrade for specific execute exam 
	 * @param executeTestID
	 * @return ArrayList<ApprovingGrade> list
	 */
	public List<ApprovingGrade> getApprovedGradeByExecuteTestID(String executeTestID)
	{
		
		aList = new ArrayList<ApprovingGrade>();
		int approveSize = JDBC.countStatment("SELECT count(*) FROM aprovinggrade ag " +
											 "WHERE ag.executeTestID='"+executeTestID+"' AND ag.teacherApproveComplete=true;");
		Object[][] result = new Object[approveSize][ApprovingGrade.NUM_OF_COLOUMS];
		JDBC.selectStatmentMatrix("SELECT * FROM aprovinggrade ag " +
								  "WHERE ag.executeTestID='"+executeTestID+"' AND ag.teacherApproveComplete=true;"
											 , result, ApprovingGrade.NUM_OF_COLOUMS, approveSize);
		for (int i = 0; i<approveSize; i++)
		{
			setApprovingGrade(result, i);
			aList.add(approvingGrade);	
		}
		return aList;
	}
	
	
	/**
	 * Return the Comments that the teacher filled when checked student exam
	 * @param studentID
	 * @param executeTestID
	 * @return String[] Comments
	 */
	public String[] getCommentByTest(String studentID,String executeTestID)
	{
		int commentSize = JDBC.countStatment("SELECT count(*) FROM teachercomment tc WHERE tc.studentID='"+studentID+"' AND tc.executeTestID='"+executeTestID+"';");
		Object[][] result = new Object[commentSize][1];
		JDBC.selectStatmentMatrix("SELECT tc.teacherComment FROM teachercomment tc WHERE tc.studentID='"+studentID+"' AND tc.executeTestID='"+executeTestID+"';"
							, result, 1,commentSize);
		String[] comments = new String[commentSize];
		for (int i = 0; i<commentSize; i++)
			if (result[i]!=null)
				comments[i] = result[i][0].toString();
			else comments[i] = null;
		
		return comments;
	}
	
	/**
	 * Return Student Object by studentID
	 * @param studentID
	 * @return Student student
	 */
	public Student getStudentByStudentID(String studentID)
	{
		student = new Student();
		Object[] result = new Object[Student.NUM_OF_COLUMNS+User.NUM_OF_COLUMNS];
		JDBC.selectStatment("SELECT * FROM student s,users u " +
							"WHERE u.userID=s.userID AND s.userID="+studentID+";"
							, result, Student.NUM_OF_COLUMNS+User.NUM_OF_COLUMNS);
		setStudent(result);
		return student;
	}
	
	/**
	 * Return StudentInTest object by specific student and specific execute test
	 * @param executeTestID
	 * @param studentID
	 * @return StudentInTest studentInTest
	 */
	public StudentInTest getStudentInTestByExecuteTestID(String executeTestID, String studentID)
	{
		
		Object[][] result = new Object[1][StudentInTest.NUM_OF_COLUMNS];
		JDBC.selectStatmentMatrix("SELECT * FROM studentintest sit " +
							"WHERE sit.studentID='"+studentID+"' AND sit.executeTestID='"+executeTestID+"';", result, StudentInTest.NUM_OF_COLUMNS,1);
		studentInTest = new StudentInTest();
		setStudentInTest(result, 0);
		studentInTest.setChoises(getStudentChoicesListByExexuteTestID(executeTestID, studentID));
		return studentInTest;
	}
	
	/**
	 * Initialize Student object from database result
	 * @param result
	 */
	public void setStudent(Object[] result)
	{
		student.setUserID(result[0].toString());
		student.setStudentAvg(Float.parseFloat(result[1].toString()));
		student.setUserLastName(result[3].toString());
		student.setUserFirstName(result[4].toString());
		student.setUserPassword(result[5].toString());
		student.setUserPermission(Integer.parseInt(result[6].toString()));
		student.setUserLogged(Boolean.parseBoolean(result[7].toString()));
		student.setUserStatus(result[8].toString());
	}
	
	/**
	 * Initialize Student object from database result
	 * @param result
	 * @param row
	 */
	public void setStudentInTest(Object[][] result, int row)
	{
		studentInTest.setExecuteTestID(result[row][0].toString());
		studentInTest.setStudent((new StudentControl().getStudentByStudentID(result[row][1].toString())));
		studentInTest.setStudentComputerGrade(Integer.parseInt(result[row][2].toString()));
		try {
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			java.util.Date parseDate = dateFormat.parse(result[row][3].toString());
			java.sql.Timestamp timestamp = new java.sql.Timestamp(parseDate.getTime());
			studentInTest.setStartTest(timestamp);
			dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			parseDate = dateFormat.parse(result[row][4].toString());
			timestamp = new java.sql.Timestamp(parseDate.getTime());
			studentInTest.setEndTest(timestamp);
			} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		studentInTest.setTestCheckedComplete(Boolean.parseBoolean(result[row][5].toString()));
		if (result[row][6] != null)
			studentInTest.setWordTestCheckedPath(result[row][6].toString());
		else studentInTest.setWordTestCheckedPath(null);
		studentInTest.setChoises(this.getStudentChoicesListByExexuteTestID(studentInTest.getExecuteTestID(), studentInTest.getStudent().getUserID()));
		
	}
	
	/**
	 * Initialize choices and set its values
	 * @param result
	 * @param row
	 */
	public void setCoices(Object[][] result,int row)
	{
		choices.setExecuteTestID(result[row][0].toString());
		choices.setStudentID(result[row][1].toString());
		choices.setQuestion((new QuestionControl()).getQuestionByQuestionID(result[row][2].toString()));
		choices.setChoice(Integer.parseInt(result[row][3].toString()));
	}
	
	/**
	 * Initialize ApprovingGrade and set its values
	 * @param result
	 * @param row
	 */
	public void setApprovingGrade(Object[][] result, int row)
	{
		approvingGrade = new ApprovingGrade();
		approvingGrade.setTeacher((new TeacherControl()).getTeacherByTeacherID(result[row][0].toString()));
		approvingGrade.setExecuteTest((new ExecuteTestControl()).getExecuteTestByExecuteTestID(result[row][1].toString()));
		//approvingGrade.setStudent((new StudentControl()).getStudentByStudentID(result[row][2].toString()));
		StudentInTest s = getStudentInTestByExecuteTestID(approvingGrade.getExecuteTest().getExecuteTestID(), result[row][2].toString());
		approvingGrade.setStudentInTest(s);
		approvingGrade.setStudentFianlGrade(Integer.parseInt(result[row][6].toString()));
		if(result[row][5]!=null)
		approvingGrade.setTeacherCommentForGrade(result[row][5].toString());
		else approvingGrade.setTeacherCommentForGrade(null);
		approvingGrade.setTeacherComment(getCommentByTest(approvingGrade.getStudentInTest().getStudent().getUserID(), approvingGrade.getExecuteTest().getExecuteTestID()));
		
	}
	

}

